package gov.nhso.prov.acc.core;

import gov.nhso.prov.acc.beans.Acc67UserBean;
import gov.nhso.prov.acc.beans.Acc6ChequeBean;
import gov.nhso.prov.acc.beans.Acc6ChequeDetailBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseBean;
import gov.nhso.prov.acc.beans.Acc6FtpBean;
import gov.nhso.prov.acc.beans.Acc6ProvinceAccountBean;
import gov.nhso.prov.acc.beans.Acc6TempChequeStmtBean;
import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.ftp.BAAC2NHSO;
import gov.nhso.prov.acc.ftp.FTPUtil;
import gov.nhso.prov.acc.ftp.NHSO2BAAC;
import gov.nhso.prov.acc.utils.BathText;
import gov.nhso.prov.acc.utils.BathThaiText;
import gov.nhso.prov.acc.utils.DataUtil;
import gov.nhso.prov.acc.utils.DateUtil;
import gov.nhso.prov.acc.utils.DigitUtil;
import gov.nhso.prov.acc.utils.PropUtil;
import gov.nhso.prov.acc.utils.Utility;

import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.ByteArrayInputStream;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.SocketException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.TimeZone;

import org.quartz.JobKey;

import com.businessobjects.report.web.event.w;
import com.crystaldecisions.sdk.framework.CrystalEnterprise;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;
//import com.sun.org.apache.xalan.internal.xsltc.DOM;
import com.zehon.FileTransferStatus;
import com.zehon.exception.FileTransferException;
import com.zehon.ftp.FTP;

@SuppressWarnings("rawtypes")
public class Test {
	private static final Locale LOCALE_TH = new Locale("TH","th");
	private static final Locale LOCALE_EN = new Locale("EN","en");
	private String ftpHost = "scap.dyndns.info";
	private String ftpUserId = "administrator";
	private String ftpPasswd = "welcome";
	private List holiDay = new ArrayList(Arrays.asList("20130101","20130225","20130401","20130408","20130415","20130416","20130501","20130506","20130524","20130701","20130722","20130812","20131023","20131205","20131210","20131231"));
	public static void main(String[] args) throws Exception {
		Connection conn = null;
		ISUD isud = null;
		Test t = new Test();
			
		try {

			Class.forName("oracle.jdbc.driver.OracleDriver");
			//conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.232:1521:NHSO", "nhso_test", "oracle2");//scap server
			conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.201.10:1549:DEVDB", "PROVACC_OWNER", "3&9CB2&060"); //NHSO DEV
			BAAC2NHSO b2n = new BAAC2NHSO();
			isud = ISUDFactory.create(conn);
			
		//	b2n.ftpBaac2NhsoTranfer(conn, isud);
		//	b2n.ftpBaac2NhsoCheque(conn, isud);
			b2n.ftpBaac2NhsoDailyStatement(conn, isud);
		//	b2n.cleanTransactionAccountType(conn, isud);
			
			//t.ftpBaac2NhsoCheque(conn, isud);
			//t.n2b(conn, isud);
			/*String allocataCode = "56010100";
			System.out.println("allocataCode >> " + allocataCode.substring(0, 6));
			PropUtil pu = new PropUtil();
			Map map = pu.statusExpense();
			Iterator it = map.entrySet().iterator();
			while (it.hasNext()) {
		        Map.Entry pairs = (Map.Entry)it.next();
		        System.out.println(pairs.getKey() + " = " + pairs.getValue());
		    }
			//BAAC2NHSO b2n = new BAAC2NHSO();
			//b2n.ftpBaac2NhsoCheque(conn, isud);
			//b2n.ftpBaac2NhsoDailyStatement(conn, isud);
			String str = "ทดสอบ Replace";
			System.out.println(str);
			System.out.println(str.replaceAll("([\\.,'\"])", ""));
		
			Date today = new Date();  

	        Calendar calendar = Calendar.getInstance();  
	        calendar.setTime(today);  

	        calendar.add(Calendar.MONTH, 1);  
	        calendar.set(Calendar.DAY_OF_MONTH, 1);  
	        calendar.add(Calendar.DATE, -1);  

	        Date lastDayOfMonth = calendar.getTime();  

	        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
	        System.out.println("Today            : " + sdf.format(today));  
	        System.out.println("Last Day of Month: " + sdf.format(lastDayOfMonth));
	        
	        Calendar cal = new GregorianCalendar(2013, 10, 0);
	        Date date = cal.getTime();
	        sdf = new SimpleDateFormat("yyyy-MM-dd");
	        System.out.println("Date : " + sdf.format(date));*/
	        
		} catch (Exception e) {
			System.out.println("Error!!");
			e.printStackTrace();
		} 
		/*Connection conn = null;
		ISUD isud = null;

		File file = new File("C:\\Users\\scapSolution\\Desktop\\HSOIN420130125CA001.05920018");
		BufferedReader bufRdr;
		try {
			FTPClient client = new FTPClient();
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.232:1521:NHSO", "nhso_test", "oracle1");
			isud = ISUDFactory.create(conn);
			client.connect("test5566.zz.mu");
			boolean login = client.login("u586791125", "09012531");
			FTPUtil ftp = new FTPUtil();
			Test t = new Test();
			// create a calendar
		    Calendar cal = Calendar.getInstance();
		    // print current time
		    System.out.println("Current year is :" + cal.getTime());
		    // set the year,month and day to something else
		    cal.set(2013, 01, 9);
		    SimpleDateFormat f = new SimpleDateFormat("dd/MM/yyyy");
		    // print the result
		     System.out.println("Altered year is :" + cal.getTime());
		     System.out.println("Altered year is :" + f.format(cal.getTime()));
			if (login) {
				//t.n2b(conn, isud);
				//t.ftpBaac2NhsoTranfer(conn, isud);
				//t.ftpBaac2NhsoCheque(conn, isud);
			}
			
			conn.commit();
		} catch (SocketException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
			Utility util = new Utility();
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.201.10:1549:DEVDB", "PROVACC_OWNER", "3&9CB2&060");
			//conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.232:1521:NHSO", "nhso_test", "oracle1");
			conn.setAutoCommit(false);
			isud = ISUDFactory.create(conn);
			bufRdr = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF8"));
			String line = null;
			StringBuffer sb = new StringBuffer();
			String buffer = "";
 			while((line = bufRdr.readLine()) != null) {
				String[] data = line.toString().split(",");
				String LIFNR = data[0];
				if (!LIFNR.equals("LIFNR") && data.length > 9) {
					if (LIFNR.matches("\\d+")) {
						for (int i = 0 ; i < (10-LIFNR.length()); i++) {
							buffer += "0";
						}
						LIFNR = buffer + LIFNR;
					}
					String BUKRS = data[1];
					String NAME1 = data[2];
					String NAME2 = data[3];
					String NAME3 = data[4];
					String NAME4 = data[5];
					String PROVINCE_ID = data[6];
					String BANKN = data[7];
					String KOINH = data[8];
					String BANKL = data[9];
					String PROVINCE_NAME = data[11];
					//.replaceAll("สำนักงานสาขาจังหวัด", "")
					sb.append("INSERT INTO PROVACC_OWNER.ACC6_PROVINCE_ACCOUNT (PROVINCE_ID,PROVINCE_VENDER_CODE,PROVINCE_VENDER_NAME,PROVINCE_NAME,PROVINCE_ACCOUNT_SAVING,PROVINCE_ACCOUNT_NAME) \n");
					sb.append("VALUES('"+PROVINCE_ID+"','"+LIFNR+"','"+NAME1+NAME2+NAME3+NAME4+"','"+PROVINCE_NAME+"','"+BANKN+"','"+KOINH+"'); \n");
					sb.append("\n");
				}
				FileWriter fstream = new FileWriter("C:\\Users\\scapSolution\\Desktop\\provinceAcc6.txt");
				BufferedWriter out = new BufferedWriter(fstream);
				out.write(sb.toString());
				out.close();
			}
			bufRdr.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
			/*bufRdr = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF8"));
			String line = null;
			StringBuffer sb = new StringBuffer();
			String buffer = "";
			while((line = bufRdr.readLine()) != null) {
				System.out.println("line.length() === > "+line.length());
			}
			FTPClient client = new FTPClient();
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.201.10:1549:DEVDB", "PROVACC_OWNER", "3&9CB2&060");
			isud = ISUDFactory.create(conn);
			client.connect("test5566.zz.mu");
			boolean login = client.login("u586791125", "09012531");
			FTPUtil ftp = new FTPUtil();
			Test t = new Test();
			if (login) {
				//t.n2b(conn, isud);
				//t.ftpBaac2NhsoTranfer(conn, isud);
				//t.ftpBaac2NhsoCheque(conn, isud);
			}
			
			conn.commit();
		} catch (SocketException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		String year = "2554";
		System.out.println("year = " + year.substring(2));
		String test = "ACC62556R6000000001";
		System.out.println(test.substring(0, test.length()-11));
		System.out.println(test.substring(test.length()-6, test.length()));
		ISUD dao = ISUDFactory.create(null);
		String code = "P@55W012D";
		System.out.println(dao.encrypt(code));
		//System.out.println(dao.decrypt(code));
*/}
		/*String tsts = "20121130.17.14.21.883000000";
		System.out.println(tsts.replaceAll("[^0-9]+", "").substring(0, 17));
		Date date = new Date();
		Calendar currentDate = Calendar.getInstance();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy MM dd",LOCALE_TH);
		date.setDate(date.getDate()+1);
		currentDate.setTime(date);
		String dateNow = formatter.format(currentDate.getTime());
	    System.out.println("Now the date is :=>  " + dateNow);*/
		/*Connection conn = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.130:1521:djopdb", "nhso", "oracle1");
			conn.setAutoCommit(false);
			ISUD dao = ISUDFactory.create(conn);
			Acc67UserBean bean = new Acc67UserBean();
			bean.setUserId("'admin'");
			bean.setPasswd("'1234'");
			bean.setUserName("'admin nhso'");
			bean.setEmail("'admin@nhso.com'");
			bean.setRoleId("1");
			dao.insert(bean);
			
			bean = new Acc67UserBean();
			bean.setUserId("'admin'");
			bean.setUpdatedDate("TO_DATE('2012-10-20 10:26:33', 'YYYY-MM-DD HH24:MI:SS')");
			dao.update(bean);
			
			bean.setUpdatedDate("");
			bean = (Acc67UserBean) dao.select(bean);
			PageBean pageBean = new PageBean();
			bean.setUserId("'admin'");
			dao.selectPage(bean, "", pageBean);
			
			dao.beanDebuger(bean);
			
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}*/
	
	public void ftpBaac2NhsoTranfer(Connection conn, ISUD isud){
		try {
			Acc6ProvinceAccountBean apab = new Acc6ProvinceAccountBean();
			Acc6TransactionBean acc6TransactionBean = new Acc6TransactionBean();
			List lsafb;
			FTPUtil fu = new FTPUtil();
			boolean result = false;
			String chequeNo = "";
			String chequeAmount = "";
			String chequeReceivingDate = "";
			String chequeType = "";
			apab.setActive("Y");
			lsafb = isud.selectList(apab, " AND PROVINCE_ACCOUNT_DAILY IS NOT NULL ");
			FTPClient client = new FTPClient();
			client.connect(this.ftpHost);
			client.login(this.ftpUserId, this.ftpPasswd);
			String remoteFolder = fu.ftpBaac2NhsoCheque+"/20130619/";
			FTPFile[] f = client.listFiles(remoteFolder);
			List<String> fftp = new ArrayList<String>();
			for (int n = 0; n < f.length; n++) {
				FTPFile ff = f[n];
				if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
					fftp.add(ff.getName());
				}
			}
		    client.disconnect();
			StringBuffer sb = new StringBuffer();
			DateUtil du = new DateUtil();
			for (int x = 0; x < lsafb.size(); x++) {
				apab = (Acc6ProvinceAccountBean) lsafb.get(x);
				String nameOfFile = "FZC009-" + apab.getProvinceAccountDaily() + "-20130619-G001";
				System.out.println("nameOfFile >> " + nameOfFile);
				InputStream in = null;
				try {
					if (fftp.contains(nameOfFile)) {
						in = FTP.getFileAsStream(nameOfFile, remoteFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
						InputStreamReader is = new InputStreamReader(in, "TIS-620");
						BufferedReader br = new BufferedReader(is);
						String read = br.readLine();
						String[] splitStr = null;
						while (read != null) {
							splitStr = read.split("\\|");
							chequeType = isud.chkData(splitStr[14]).trim();
							chequeNo = isud.chkData(splitStr[12]).trim();
							chequeAmount = isud.chkData(splitStr[13]).trim();
							chequeReceivingDate = du.changeDateToDB(fu.changeFormatDate(isud.chkData(splitStr[3]).trim()));
							// get data form Acc6Transaction
							if (chequeType.equals("D")) {
								result = false;
								acc6TransactionBean = new Acc6TransactionBean();
								acc6TransactionBean.setChequeNo(chequeNo);
								acc6TransactionBean.setExpenseAmount(chequeAmount);
								Map<String, String> xcols = new HashMap<String, String>();
								xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
								acc6TransactionBean = (Acc6TransactionBean) isud.select(acc6TransactionBean, xcols, " AND REASON_CODE IS NULL ");
								
								Acc6TransactionBean atb = new Acc6TransactionBean();
								if (acc6TransactionBean.getTransactionTimestamp() != null) {
									atb.setChequeReceivingDate(chequeReceivingDate);
									atb.setReasonCode("00");
									atb.setTransactionTimestamp("TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
									result = isud.update(atb , " AND PROVINCE_ID = '"+apab.getProvinceId()+"' AND CHEQUE_NO = '"+chequeNo+"' AND REASON_CODE IS NULL ");
									isud.execDataSql("UPDATE " + fu.schema + ".ACC6_EXPENSE_CHEQUE SET CHEQUE_RECEIVING_DATE = "+chequeReceivingDate+" WHERE CHEQUE_NO = '" + chequeNo + "' AND CHEQUE_AMOUNT = " + chequeAmount + " AND TRANSACTION_TIMESTAMP = TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
								}
								System.out.println("result >> " + result);
								if (!result) {
									sb.delete(0, sb.length());
									sb.append("INSERT INTO " + fu.schema + ".ACC6_TEMP_CHEQUE_STMT (PROVINCE_ID,CHEQUE_NO,CHEQUE_AMOUNT,CHEQUE_RECEIVING_DATE,CREATED_DATE) ");
									sb.append("VALUES ('"+apab.getProvinceId()+"','"+chequeNo+"',"+chequeAmount+","+chequeReceivingDate+",(SYSDATE)) ");
									isud.execDataSql(sb.toString());
									System.out.println("sql1 >> " + sb.toString());
								}
							}
						    read = br.readLine();
						}
						is.close();
					}
				} catch (Exception e) {
					System.out.println("printStackTrace Class BACC2NHSO Method ftpBaac2NhsoCheque");
					e.printStackTrace();
				} finally {
					try {
						if (in != null) {
							in.close();
						}
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
			
			Acc6TempChequeStmtBean tcs = new Acc6TempChequeStmtBean();
			lsafb = isud.selectList(tcs);
			if (lsafb.size() > 0) {
				du = new DateUtil();
				for (int i=0;i<lsafb.size();i++) {
					Acc6TempChequeStmtBean bean = (Acc6TempChequeStmtBean)lsafb.get(i);
					result = false;
					acc6TransactionBean = new Acc6TransactionBean();
					acc6TransactionBean.setProvinceId(bean.getProvinceId());
					acc6TransactionBean.setChequeNo(bean.getChequeNo());
					acc6TransactionBean.setExpenseAmount(bean.getChequeAmount());
					Map<String, String> xcols = new HashMap<String, String>();
					xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
					acc6TransactionBean = (Acc6TransactionBean) isud.select(acc6TransactionBean, xcols, " AND REASON_CODE IS NULL ");
					
					Acc6TransactionBean atb = new Acc6TransactionBean();
					if (acc6TransactionBean.getTransactionTimestamp() != null) {
						atb.setChequeReceivingDate("TO_DATE('"+du.changeDateFromDB(bean.getChequeReceivingDate(), "")+"','DD.MM.YYYY')");
						atb.setReasonCode("00");
						atb.setTransactionTimestamp("TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
						result = isud.update(atb , " AND PROVINCE_ID = '"+bean.getProvinceId()+"' AND CHEQUE_NO = '"+bean.getChequeNo()+"' AND REASON_CODE IS NULL ");
					}
					
					if (result) {
						Map data = isud.findDataSql("SELECT DOC_NO FROM " + fu.schema + ".ACC6_EXPENSE_CHEQUE WHERE CHEQUE_NO = '" + bean.getChequeNo() + "' AND CHEQUE_AMOUNT = " + bean.getChequeAmount() + " AND TRANSACTION_TIMESTAMP = TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
						String docNo = isud.chkData(data.get("DOC_NO"));
						if (!docNo.equals("")) {
							isud.execDataSql("UPDATE " + fu.schema + ".ACC6_EXPENSE_CHEQUE SET CHEQUE_RECEIVING_DATE = TO_DATE('"+du.changeDateFromDB(bean.getChequeReceivingDate(), "")+"','DD.MM.YYYY') WHERE CHEQUE_NO = '" + bean.getChequeNo() + "' AND CHEQUE_AMOUNT = " + bean.getChequeAmount() + " AND TRANSACTION_TIMESTAMP = TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
							isud.execDataSql("DELETE " + fu.schema + ".ACC6_TEMP_CHEQUE_STMT WHERE PROVINCE_ID = '"+bean.getProvinceId()+"' AND CHEQUE_NO='"+bean.getChequeNo()+"' AND CHEQUE_AMOUNT = "+bean.getChequeAmount()+" AND TO_DATE(CHEQUE_RECEIVING_DATE,'DD.MM.YYYY') = TO_DATE('"+du.changeDateFromDB(bean.getChequeReceivingDate(), "")+"','DD.MM.YYYY') ");
						}
					}
				}
			}
			
			String sql = "SELECT DOC_NO,SUM_CHEQUE_AMOUNT FROM " + fu.schema + ".ACC6_CHEQUE WHERE CHEQUE_STATUS = 'W' ";
			List lsDocNo = isud.listDataSql(sql);
			for (int n = 0; n < lsDocNo.size(); n++) {
				Map map = (Map) lsDocNo.get(n);
				String docNo = isud.chkData(map.get("DOC_NO"));
				String sumChequeAmount1 = isud.chkData(map.get("SUM_CHEQUE_AMOUNT"));
				String sumChequeAmount2 = isud.chkData(isud.findDataSql("SELECT SUM(CHEQUE_AMOUNT) AS SUM_CHEQUE_AMOUNT FROM " + fu.schema + ".ACC6_CHEQUE_DETAIL A, " + fu.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND B.REASON_CODE = '00' ").get("SUM_CHEQUE_AMOUNT"));
				if (sumChequeAmount1.equals(sumChequeAmount2)) {
					Acc6ExpenseBean aeb = new Acc6ExpenseBean();
					aeb.setDocNo(docNo);
					aeb.setExpenseStatus("Y");
					aeb.setUpdatedBy("BAAC");
					aeb.setUpdatedDate("(SYSDATE)");
					isud.update(aeb);
				}
			}

			System.out.println("============================= printMessage =============================");
			isud.printMessage();
			System.out.println("=========================== end printMessage ===========================");
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}

	public void ftpBaac2NhsoCheque(Connection conn, ISUD isud){
		try {
			List lsafb;
			Acc6ProvinceAccountBean apab = new Acc6ProvinceAccountBean();
			Acc6TransactionBean acc6TransactionBean = new Acc6TransactionBean();
			apab.setActive("Y");
			lsafb = isud.selectList(apab, " AND PROVINCE_ACCOUNT_DAILY IS NOT NULL ");
			FTPClient client = new FTPClient();
			int returnCode = 0;
			Map mapResult = new HashMap();
			int countResult = 0;
			boolean result = false;
			String chequeNo = "";
			String chequeAmount = "";
			String chequeReceivingDate = "";
			String chequeType = "";
			String chqStmtKey = "";
			StringBuffer sb = new StringBuffer();
			FTPUtil ftpU = new FTPUtil();
			client.connect(ftpU.ftpHost);
			client.login(ftpU.ftpUserId, ftpU.ftpPasswd);
			String remoteFolder = ftpU.ftpBaac2NhsoCheque+"/20130829/";
			FTPFile[] f = client.listFiles(remoteFolder);
			List<String> fftp = new ArrayList<String>();
			for (int n = 0; n < f.length; n++) {
				FTPFile ff = f[n];
				if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
					fftp.add(ff.getName());
				}
			}
			DateUtil du = new DateUtil();
			for (int x = 0; x < lsafb.size(); x++) {
				apab = (Acc6ProvinceAccountBean) lsafb.get(x);
				String nameOfFile = "FZC009-" + apab.getProvinceAccountDaily() + "-20130719-G001";
				System.out.println("nameOfFile >> " + nameOfFile);
				InputStream in = null;
				String txtCondition = "";
				try {
					if (fftp.contains(nameOfFile)) {
						in = FTP.getFileAsStream(nameOfFile, remoteFolder, ftpU.ftpHost,  ftpU.ftpUserId,  ftpU.ftpPasswd);
						InputStreamReader is = new InputStreamReader(in, "TIS-620");
						BufferedReader br = new BufferedReader(is);
						String read = br.readLine();
						String[] splitStr = null;
						while (read != null) {
							System.out.println("read >> " + read);
							splitStr = read.split("\\|");
							chequeType = isud.chkData(splitStr[14]).trim();
							chequeNo = isud.chkData(splitStr[12]).trim();
							chequeAmount = isud.chkData(splitStr[13]).trim();
							chequeReceivingDate = du.changeDateToDB(ftpU.changeFormatDate(isud.chkData(splitStr[3]).trim()));
							chqStmtKey = isud.chkData(splitStr[3]).trim() + isud.chkData(splitStr[4]).trim();
							// get data form Acc6Transaction
							System.out.println("chequeType >> " + chequeType);
							if (chequeType.equals("D")) {
								result = false;
								acc6TransactionBean = new Acc6TransactionBean();
								acc6TransactionBean.setChequeNo(chequeNo);
								acc6TransactionBean.setExpenseAmount(chequeAmount);
								Map<String, String> xcols = new HashMap<String, String>();
								xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
								acc6TransactionBean = (Acc6TransactionBean) isud.select(acc6TransactionBean, xcols, " AND REASON_CODE IS NULL ");
								Acc6TransactionBean atb = new Acc6TransactionBean();
								if (acc6TransactionBean.getTransactionTimestamp() != null) {
									atb.setChequeReceivingDate(chequeReceivingDate);
									atb.setChqStmtKey(chqStmtKey);
									atb.setReasonCode("00");
									atb.setTransactionTimestamp("TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
									result = isud.update(atb , " AND PROVINCE_ID = '"+apab.getProvinceId()+"' AND CHEQUE_NO = '"+chequeNo+"' AND REASON_CODE IS NULL ");
									isud.execDataSql("UPDATE " + ftpU.schema + ".ACC6_EXPENSE_CHEQUE SET CHEQUE_RECEIVING_DATE = "+chequeReceivingDate+" WHERE CHEQUE_NO = '" + chequeNo + "' AND CHEQUE_AMOUNT = " + chequeAmount + " AND TRANSACTION_TIMESTAMP = TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
									sb.setLength(0);
									sb.append("DELETE FROM "+ftpU.schema+".ACC6_TEMP_CHEQUE_STMT ");
									sb.append("WHERE PROVINCE_ID = '"+apab.getProvinceId()+"' AND CHEQUE_NO = '"+chequeNo+"' AND CHEQUE_AMOUNT = "+chequeAmount+" ");
									sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+"");
									isud.execDataSql(sb.toString());
								} else if (!chequeNo.equals("")) { //multiple cheque
									sb.setLength(0);
									sb.append("SELECT SUM_CHEQUE_AMOUNT ");
									sb.append("FROM "+ftpU.schema+".ACC6_CHEQUE ");
									sb.append("WHERE PROVINCE_ID = '"+apab.getProvinceId()+"' AND CHEQUE_NO = '"+chequeNo+"' AND SUM_CHEQUE_AMOUNT = " + chequeAmount + " ");
									sb.append("AND CHEQUE_STATUS = 'W' AND ACTIVE = 'Y' ");
									Map map = isud.findDataSql(sb.toString());
									if (!isud.chkData(map.get("SUM_CHEQUE_AMOUNT")).equals("")) {
										double sumChequeAmount = Double.parseDouble(map.get("SUM_CHEQUE_AMOUNT").toString());
										double dChequeAmount = Double.parseDouble(chequeAmount);
										if (sumChequeAmount == dChequeAmount) {
											sb.setLength(0);
											sb.append("UPDATE " + ftpU.schema + ".ACC6_TRANSACTION ");
											sb.append("SET CHEQUE_RECEIVING_DATE = " + chequeReceivingDate + ",REASON_CODE = '00',CHQ_STMT_KEY = '" + chqStmtKey + "' ");
											sb.append("WHERE PROVINCE_ID = '"+apab.getProvinceId()+"' AND CHEQUE_NO = '"+chequeNo+"'");
											result = isud.execDataSql(sb.toString());
											sb.setLength(0);
											sb.append("DELETE FROM "+ftpU.schema+".ACC6_TEMP_CHEQUE_STMT ");
											sb.append("WHERE PROVINCE_ID = '"+apab.getProvinceId()+"' AND CHEQUE_NO = '"+chequeNo+"' AND CHEQUE_AMOUNT = "+chequeAmount+" ");
											sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+"");
											isud.execDataSql(sb.toString());
											isud.printMessage();
										}
									}
								}
								if (chequeNo.equals("")) {
									txtCondition = " AND CHEQUE_NO IS NULL ";
								} else {
									txtCondition = " AND CHEQUE_NO = '"+chequeNo+"' ";
								}
								
								countResult = 0;
								sb.setLength(0);
								sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+ftpU.schema+".ACC6_TRANSACTION ");
								sb.append("WHERE PROVINCE_ID = '"+apab.getProvinceId()+"' AND CHEQUE_NO = '"+chequeNo+"' AND EXPENSE_AMOUNT = "+chequeAmount+" ");
								sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+"");
								mapResult = isud.findDataSql(sb.toString());
								countResult = Integer.parseInt(isud.chkData(mapResult.get("COUNT_CHEQUE_NO")));
								if (countResult <= 0) {
									sb.setLength(0);
									sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+ftpU.schema+".ACC6_TRANSACTION ");
									sb.append("WHERE CHQ_STMT_KEY = '"+chqStmtKey+"' ");
									mapResult = isud.findDataSql(sb.toString());
									countResult = Integer.parseInt(isud.chkData(mapResult.get("COUNT_CHEQUE_NO")));
									if (countResult <= 0) {
										sb.setLength(0);
										sb.append("SELECT COUNT(PROVINCE_ID) AS COUNT_CHEQUE_NO FROM "+ftpU.schema+".ACC6_TEMP_CHEQUE_STMT ");
										sb.append("WHERE PROVINCE_ID = '"+apab.getProvinceId()+"' "+txtCondition+" AND CHEQUE_AMOUNT = "+chequeAmount+" ");
										sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+" AND CHQ_STMT_KEY = '"+chqStmtKey+"' ");
										mapResult = isud.findDataSql(sb.toString());
										countResult = Integer.parseInt(isud.chkData(mapResult.get("COUNT_CHEQUE_NO")));
										System.out.println("countResult >> " + countResult + " result >> " + result);
										System.out.println("chequeNo >> " + chequeNo + " chequeAmount >> " + chequeAmount);
										if (!result && countResult <= 0) {
											sb.delete(0, sb.length());
											sb.append("INSERT INTO " + ftpU.schema + ".ACC6_TEMP_CHEQUE_STMT (PROVINCE_ID,CHEQUE_NO,CHEQUE_AMOUNT,CHEQUE_RECEIVING_DATE,CREATED_DATE,CHQ_STMT_KEY) ");
											sb.append("VALUES ('"+apab.getProvinceId()+"','"+chequeNo+"',"+chequeAmount+","+chequeReceivingDate+",(SYSDATE),'"+chqStmtKey+"') ");
											isud.execDataSql(sb.toString());
											System.out.println("sql line 506 >> " + sb.toString());
										}
									}
								}
							}
						    read = br.readLine();
						}
						is.close();
					}else {
						System.out.println("File Not Found");
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						if (in != null) {
							in.close();
						}
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
			String sql = "";
			List lsDocNo = new ArrayList(); 
			Acc6TempChequeStmtBean tcs = new Acc6TempChequeStmtBean();
			lsafb = isud.selectList(tcs);
			if (lsafb.size() > 0) {
				for (int i=0;i<lsafb.size();i++) {
					Acc6TempChequeStmtBean bean = (Acc6TempChequeStmtBean)lsafb.get(i);
					result = false;
					acc6TransactionBean = new Acc6TransactionBean();
					acc6TransactionBean.setProvinceId(bean.getProvinceId());
					acc6TransactionBean.setChequeNo(bean.getChequeNo());
					acc6TransactionBean.setExpenseAmount(bean.getChequeAmount());
					Map<String, String> xcols = new HashMap<String, String>();
					xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
					acc6TransactionBean = (Acc6TransactionBean) isud.select(acc6TransactionBean, xcols, " AND REASON_CODE IS NULL ");
					
					Acc6TransactionBean atb = new Acc6TransactionBean();
					if (acc6TransactionBean.getTransactionTimestamp() != null) {
						atb.setChequeReceivingDate("TO_DATE('"+du.changeDateFromDB(bean.getCreatedDate(), "")+"','DD.MM.YYYY')");
						atb.setReasonCode("00");
						atb.setTransactionTimestamp("TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
						result = isud.update(atb , " AND PROVINCE_ID = '"+bean.getProvinceId()+"' AND CHEQUE_NO = '"+bean.getChequeNo()+"' AND REASON_CODE IS NULL ");
						if (result) {
							Map data = isud.findDataSql("SELECT DOC_NO FROM " + ftpU.schema + ".ACC6_EXPENSE_CHEQUE WHERE CHEQUE_NO = '" + bean.getChequeNo() + "' AND CHEQUE_AMOUNT = " + bean.getChequeAmount() + " AND TRANSACTION_TIMESTAMP = TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
							String docNo = isud.chkData(data.get("DOC_NO"));
							if (!docNo.equals("")) {
								isud.execDataSql("UPDATE " + ftpU.schema + ".ACC6_EXPENSE_CHEQUE SET CHEQUE_RECEIVING_DATE = TO_DATE('"+du.changeDateFromDB(bean.getChequeReceivingDate(), "")+"','DD.MM.YYYY') WHERE CHEQUE_NO = '" + bean.getChequeNo() + "' AND CHEQUE_AMOUNT = " + bean.getChequeAmount() + " AND TRANSACTION_TIMESTAMP = TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
								isud.execDataSql("DELETE " + ftpU.schema + ".ACC6_TEMP_CHEQUE_STMT WHERE PROVINCE_ID = '"+bean.getProvinceId()+"' AND CHEQUE_NO='"+bean.getChequeNo()+"' AND CHEQUE_AMOUNT = "+bean.getChequeAmount()+" AND TRUNC(CHEQUE_RECEIVING_DATE) = TO_DATE('"+du.changeDateFromDB(bean.getChequeReceivingDate(), "")+"','DD.MM.YYYY') ");
							}
						}
					} else {
						List ls = new ArrayList();
						Acc6ChequeBean acb = new Acc6ChequeBean();
						acb.setChequeNo(bean.getChequeNo());
						acb.setSumChequeAmount(bean.getChequeAmount());
						acb.setProvinceId(bean.getProvinceId());
						acb.setActive("Y");
						acb.setChequeStatus("W");
						isud.select(acb);
						if (acb.getDocNo() != null && !acb.getDocNo().equals("")) {
							Acc6ChequeDetailBean acbd = new Acc6ChequeDetailBean();
							acbd.setDocNo(acb.getDocNo());
							xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
							ls = isud.selectList(acbd, xcols, "");
							for (int j = 0 ; j < ls.size() ; j++ ) {
								atb = new Acc6TransactionBean();
								Acc6ChequeDetailBean acc6ChequeDetailBean = (Acc6ChequeDetailBean) ls.get(j);
								atb.setChequeReceivingDate("TO_DATE('"+du.changeDateFromDB(bean.getCreatedDate(), "")+"','DD.MM.YYYY')");
								atb.setReasonCode("00");
								atb.setTransactionTimestamp("TO_TIMESTAMP('" + acc6ChequeDetailBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
								result = isud.update(atb , " AND PROVINCE_ID = '"+bean.getProvinceId()+"' AND CHEQUE_NO = '"+bean.getChequeNo()+"' AND REASON_CODE IS NULL ");
							}
							if (result) {
								isud.execDataSql("UPDATE " + ftpU.schema + ".ACC6_CHEQUE SET CHEQUE_RECEIVING_DATE = TO_DATE('"+du.changeDateFromDB(bean.getChequeReceivingDate(), "")+"','DD.MM.YYYY') WHERE DOC_NO = '" + acb.getDocNo() + "' ");
								isud.execDataSql("DELETE " + ftpU.schema + ".ACC6_TEMP_CHEQUE_STMT WHERE PROVINCE_ID = '"+bean.getProvinceId()+"' AND CHEQUE_NO='"+bean.getChequeNo()+"' AND CHEQUE_AMOUNT = "+bean.getChequeAmount()+" AND TRUNC(CHEQUE_RECEIVING_DATE) = TO_DATE('"+du.changeDateFromDB(bean.getChequeReceivingDate(), "")+"','DD.MM.YYYY') ");
							}
						}
					}
				}
				
			}

			sql = "SELECT DOC_NO FROM " + ftpU.schema + ".ACC6_EXPENSE WHERE EXPENSE_STATUS IN ('W','S') AND EXPENSE_TYPE = 'C'";
			lsDocNo = isud.listDataSql(sql);
			for (int n = 0; n < lsDocNo.size(); n++) {
				Map map = (Map) lsDocNo.get(n);
				String docNo = isud.chkData(map.get("DOC_NO"));
				String num1 = isud.chkData(isud.findDataSql("SELECT COUNT(*) AS NUM FROM " + ftpU.schema + ".ACC6_EXPENSE_CHEQUE WHERE DOC_NO = '"  + docNo + "'").get("NUM"));
				String num2 = isud.chkData(isud.findDataSql("SELECT COUNT(*) AS NUM FROM " + ftpU.schema + ".ACC6_EXPENSE_CHEQUE A, " + ftpU.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND B.REASON_CODE = '00' ").get("NUM"));
				String num3 = isud.chkData(isud.findDataSql("SELECT COUNT(*) AS NUM FROM " + ftpU.schema + ".ACC6_EXPENSE_CHEQUE A, " + ftpU.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND (B.REASON_CODE <> '00' AND B.REASON_CODE IS NOT NULL) ").get("NUM"));
				if (!num2.equals("0")) {
					Acc6ExpenseBean aeb = new Acc6ExpenseBean();
					aeb.setDocNo(docNo);
					if (num1.equals(num2)) {
						aeb.setExpenseStatus("Y");
					} else {
						aeb.setExpenseStatus("S");
					}
					aeb.setUpdatedBy("BAAC");
					aeb.setUpdatedDate("(SYSDATE)");
					isud.update(aeb);
				} else if (!num3.equals("0") && num1.equals(num3)) {
					Acc6ExpenseBean aeb = new Acc6ExpenseBean();
					aeb.setDocNo(docNo);
					aeb.setExpenseStatus("N");
					aeb.setUpdatedBy("BAAC");
					aeb.setUpdatedDate("(SYSDATE)");
					isud.update(aeb);
				}
			}

			sql = "SELECT DOC_NO,SUM_CHEQUE_AMOUNT,PROVINCE_ID,CHEQUE_NO FROM " + ftpU.schema + ".ACC6_CHEQUE WHERE CHEQUE_STATUS = 'W' ";
			sb = new StringBuffer();
			lsDocNo = isud.listDataSql(sql);
			for (int n = 0; n < lsDocNo.size(); n++) {
				Map map = (Map) lsDocNo.get(n);
				chequeNo = isud.chkData(map.get("CHEQUE_NO"));
				String docNo = isud.chkData(map.get("DOC_NO"));
				String provinceId = isud.chkData(map.get("PROVINCE_ID"));
				String sumChequeAmount1 = isud.chkData(map.get("SUM_CHEQUE_AMOUNT"));
				String sumChequeAmount2 = isud.chkData(isud.findDataSql("SELECT SUM(CHEQUE_AMOUNT) AS SUM_CHEQUE_AMOUNT FROM " + ftpU.schema + ".ACC6_CHEQUE_DETAIL A, " + ftpU.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND B.REASON_CODE = '00' ").get("SUM_CHEQUE_AMOUNT"));
				if (sumChequeAmount1.equals(sumChequeAmount2)) {
					//get CHEQUE_RECEIVING_DATE from table Acc6Transaction
					sb.setLength(0);
					sb.append("SELECT DISTINCT CHEQUE_RECEIVING_DATE ");
					sb.append("FROM "+ftpU.schema+".ACC6_TRANSACTION ");
					sb.append("WHERE CHEQUE_NO = '" + chequeNo + "' AND PROVINCE_ID = '" + provinceId + "' ");
					sb.append("AND REASON_CODE = '00' ");
					Map mapChequeReceivingDate = isud.findDataSql(sb.toString()); //CHEQUE_RECEIVING_DATE
					if (!isud.chkData(mapChequeReceivingDate.get("CHEQUE_RECEIVING_DATE")).equals("")) {
						Acc6ChequeBean acb = new Acc6ChequeBean();
						acb.setDocNo(docNo);
						acb.setChequeStatus("Y");
						acb.setUpdatedDate("(SYSDATE)");
						acb.setUpdatedBy("BAAC");
						acb.setChequeReceivingDate("TO_DATE('"+du.changeDateFromDB(mapChequeReceivingDate.get("CHEQUE_RECEIVING_DATE").toString(), "")+"','DD.MM.YYYY')");
						isud.update(acb);
					}
				}
			}
			isud.printMessage();
			conn.commit();
		} catch (Exception e1) {
			isud.printMessage();
			e1.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	@SuppressWarnings("unchecked")
	public void n2b(Connection conn, ISUD isud){
		Acc6ProvinceAccountBean apab = new Acc6ProvinceAccountBean();
		apab.setActive("Y");
		List lsapab;
		NHSO2BAAC nsb = new NHSO2BAAC();
		try {
			lsapab = isud.selectList(apab);
			int cf = 0;
			int countIn = 0;
			int countOut = 0;
			double sumAmtIn = 0;
			double sumAmtOut = 0;
			//TRANSFER
			for (int i = 0; i < lsapab.size(); i++) {

				StringBuffer header = new StringBuffer();
				StringBuffer detail = new StringBuffer();
				StringBuffer total = new StringBuffer();
				
				StringBuffer text = new StringBuffer();
				
				apab = (Acc6ProvinceAccountBean) lsapab.get(i);
				
				//HEADER RECORD sb.append
				header.delete(0, header.length());
				detail.delete(0, detail.length());
				total.delete(0, total.length());
				text.delete(0, text.length());
				header.append(nsb.fulFill("0", 1));
				header.append(nsb.fulFill("000001", 6));
				header.append(nsb.fulFill(apab.getProvinceVenderName(), 30));
				header.append(nsb.fulFill(apab.getProvinceAccountName(), 40));
				header.append(nsb.fulFill(apab.getProvinceAccountSaving(), 16));
				header.append(nsb.fulFill(nsb.yyyy() + nsb.mm() + nsb.dd(), 8));
				header.append(nsb.fulFill(" ", 171));
				header.append(nsb.fulFill("100", 3));
				header.append(nsb.fulFill(" ", 125));
				header.append("\r\n");
				
				//DETAIL RECORDS
				String sql = "SELECT B.VENDER_BANK_CODE, B.VENDER_BANK_BRANCH_CODE, B.VENDER_ACCOUNT, TO_CHAR(A.TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP " +
				" , A.EXPENSE_AMOUNT,  B.VENDER_ACCOUNT_NAME_TH,  B.VENDER_ACCOUNT_ADDRESS_TH,  B.VENDER_ACCOUNT_NAME_EN,  B.VENDER_ACCOUNT_ADDRESS_EN" +
				" FROM ACC6_TRANSACTION A JOIN ACC6_VENDER B ON A.VENDER_CODE = B.VENDER_CODE " +
				" WHERE A.EXPENSE_DATE = TO_DATE('"+nsb.yyyy()+"-"+nsb.mm()+"-15', 'YYYY-MM-DD') " +
				" AND A.PROVINCE_ID = '" + apab.getProvinceId() + "' AND B.VENDER_BANK_CODE = '034' " +
				" AND A.TRANSACTION_TYPE IN ('T', 'A')";
				List lsatb = isud.listDataSql(sql);
				Map atb = null;
				double sum = 0;
				String amount = "";
				String net = "";
				String branchCode = "";
				if (lsatb.size() > 0) {
					for (int j = 0; j < lsatb.size(); j++){
						atb = (Map) lsatb.get(j);
						amount = isud.chkData(atb.get("EXPENSE_AMOUNT"));
						net = amount;
						sum = sum + Double.parseDouble(amount);
						
						String accNo = isud.chkData(atb.get("VENDER_ACCOUNT"));
						String bankCode = isud.chkData(atb.get("VENDER_BANK_CODE"));
						
						branchCode = "";
						if (bankCode.equals("034")) {
							if (accNo.trim().length() == 10) {
								branchCode = "0" + accNo.substring(0, 3);
							} else if (accNo.trim().length() == 12) {
								branchCode = "0" + accNo.substring(2, 5);
							}
						} else {
							branchCode = isud.chkData(atb.get("VENDER_BANK_BRANCH_CODE"));
							if (branchCode != null && !branchCode.equals("")) {
								if (branchCode.trim().length() < 4) {
									for (int x=0;x<(4-branchCode.trim().length());x++) {
										branchCode = "0"+branchCode;
									}
								}
							}else {
								if (accNo.trim().length() == 10) {
									branchCode = "0" + accNo.substring(0, 3);
								} else if (accNo.trim().length() == 12) {
									branchCode = "0" + accNo.substring(2, 5);
								}
							}
						}

						detail.append(nsb.fulFill("1", 1));
						detail.append(nsb.fulFill(nsb.numberFormat("000000", String.valueOf(j + 2)), 6));
						detail.append(nsb.fulFill(nsb.yyyy() + nsb.mm() + nsb.dd(), 8));
						detail.append(nsb.fulFill(bankCode, 3));
						detail.append(nsb.fulFill(branchCode, 4));
						detail.append(nsb.fulFill(accNo, 16));
						detail.append(nsb.fulFill("C", 1));
						detail.append(nsb.fulFill(nsb.numberFormat("0000000000.00",  amount).replaceAll("\\.", ""), 12));
						detail.append(nsb.fulFill("N", 1));
						detail.append(nsb.fulFill(nsb.numberFormat("0000000", "0"), 7));
						detail.append(nsb.fulFill(nsb.numberFormat("0000000", "0"), 7));
						detail.append(nsb.fulFill(nsb.numberFormat("0000000", "0"), 7));
						detail.append(nsb.fulFill(nsb.numberFormat("0000000000.00", net).replaceAll("\\.", ""), 12));
						detail.append(nsb.fulFill("??", 2));
						detail.append(nsb.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_TH")), 40));
						detail.append(nsb.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_TH")), 60));
						detail.append(nsb.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_EN")), 40));
						detail.append(nsb.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_EN")), 60));
						detail.append(nsb.fulFill(isud.chkData(atb.get("TRANSACTION_TIMESTAMP")), 81));
						detail.append(nsb.fulFill(" ",32));
						detail.append("\r\n");
						sumAmtIn += Double.parseDouble(amount);
						countIn++;
					}
				}
				//TOTAL RECORD
				total.append(nsb.fulFill("2",1));
				total.append(nsb.fulFill(nsb.numberFormat("000000", String.valueOf(lsatb.size() + 2)),6));
				total.append(nsb.fulFill(nsb.numberFormat("00000000", String.valueOf(lsatb.size())),8));
				total.append(nsb.fulFill(nsb.formatDecimal(sum,2),15));
				total.append(nsb.fulFill(" ",242));
				total.append(nsb.fulFill(" ",128));
				if (lsatb.size() > 0) {
					cf++;
					branchCode = "";
					if (apab.getProvinceAccountSaving().trim().length() == 10) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(0, 3);
					} else if (apab.getProvinceAccountSaving().trim().length() == 12) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(2, 5);
					}
					String[] arrStr = ConfigBundle.getValue("BRANCH_CODE").split("\\,");
					if (arrStr.length > 1) {
						for (int x=0;x<arrStr.length;x++) {
							String[] arrBuffer = arrStr[x].split("\\|");
							if (apab.getProvinceId().equals(arrBuffer[0])) {
								branchCode = arrBuffer[1];
								break;
							}
						}
					}
					text.append(header).append(detail).append(total);
					InputStream is = new ByteArrayInputStream(text.toString().getBytes("TIS-620"));
					String destFolder = "/FromNhso/"+nsb.yyyy()+"/"+nsb.mm()+"/"; 
					String nameOfFile = "HSOIN4"+nsb.yyyy()+nsb.mm()+nsb.dd()+"CA" + nsb.numberFormat("000", String.valueOf(cf)) + ".0592"+branchCode;
					/*FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
					FileWriter file = new FileWriter(nsb.ftpBackup+"\\"+nameOfFile);
					BufferedWriter out = new BufferedWriter(file);
					out.write(text.toString());
					out.close();*/
					Acc6FtpBean afb = new Acc6FtpBean();
					afb.setFileName(nameOfFile);
					List ls = isud.selectList(afb);
					if (ls.size() == 0) {
						try {
							int status = FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
							if (FileTransferStatus.SUCCESS == status) {
								afb.setFtpDesc("got ftp-ed successfully to  folder " + destFolder + "/" + nameOfFile);
							} else if(FileTransferStatus.FAILURE == status) {
								afb.setFtpDesc("Fail to ftp  to  folder "+destFolder);
								afb.setFtpStatus("F");
							}
						} catch (Exception e) {
							afb.setFtpDesc("Fail to ftp  to  folder "+e.getMessage());
							afb.setFtpStatus("F");
							e.printStackTrace();
						}
						isud.insert(afb);
					}
				}
				
				//HEADER RECORD sb.append
				header.delete(0, header.length());
				detail.delete(0, detail.length());
				total.delete(0, total.length());
				text.delete(0, text.length());
				
				sql = "SELECT NVL(YYYY,TO_CHAR(SYSDATE, 'YYYY')) || MM || CASE WHEN LENGTH(DD) = 1 THEN '0'||DD ELSE DD END AS HOLIDAYS FROM ACC6_BAAC_HOLIDAY ";
				List holiday = isud.listDataSql(sql);
				List lsHoliday = new ArrayList();
				for (int x = 0 ; x < holiday.size() ; x++) {
					Map map  = (Map) holiday.get(x);
					lsHoliday.add(isud.chkData(map.get("HOLIDAYS")));
				}
				
				FTPUtil ftu = new FTPUtil();
				int count = 1;
				boolean result = ftu.getWeekEnd(nsb.yyyy(), nsb.mm(), ftu.getTomorrow());
				String nextDay = "";
				String strHoliDay = ftu.getNextDayFullFormat(count);
				String[] strArr = ftu.getTomorrow(count).split("\\/");
				while (result || lsHoliday.contains(strHoliDay)) {//เป็นวันเสาร์ -วันอาทิตย์ หรือวันหยุด
					count++;
					nextDay = ftu.getTomorrow(count);
					strArr = nextDay.split("\\/");
					result = ftu.getWeekEnd(strArr[2], strArr[1], strArr[0]);
					strHoliDay = ftu.getNextDayFullFormat(count);
				}
				header.append(nsb.fulFill("0", 1));
				header.append(nsb.fulFill("000001", 6));
				header.append(nsb.fulFill(apab.getProvinceVenderName(), 30));
				header.append(nsb.fulFill(apab.getProvinceAccountName(), 40));
				header.append(nsb.fulFill(apab.getProvinceAccountSaving(), 16));
				header.append(nsb.fulFill(strArr[2] + strArr[1] + strArr[0], 8));
				header.append(nsb.fulFill(" ", 171));
				header.append(nsb.fulFill("100", 3));
				header.append(nsb.fulFill(" ", 125));
				header.append("\r\n");
				
				//DETAIL RECORDS
				sql = "SELECT B.VENDER_BANK_CODE, B.VENDER_BANK_BRANCH_CODE, B.VENDER_ACCOUNT, TO_CHAR(A.TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP " +
				" , A.EXPENSE_AMOUNT,  B.VENDER_ACCOUNT_NAME_TH,  B.VENDER_ACCOUNT_ADDRESS_TH,  B.VENDER_ACCOUNT_NAME_EN,  B.VENDER_ACCOUNT_ADDRESS_EN" +
				" FROM ACC6_TRANSACTION A JOIN ACC6_VENDER B ON A.VENDER_CODE = B.VENDER_CODE " +
				" WHERE A.EXPENSE_DATE = TO_DATE('"+nsb.yyyy()+"-"+nsb.mm()+"-"+nsb.dd()+"', 'YYYY-MM-DD') " +
				" AND A.PROVINCE_ID = '" + apab.getProvinceId() + "' AND B.VENDER_BANK_CODE <> '034' " +
				" AND A.TRANSACTION_TYPE IN ('T', 'A')";
				lsatb = isud.listDataSql(sql);
				atb = null;
				sum = 0;
				amount = "";
				net = "";
				branchCode = "";
				if (lsatb.size() > 0) {
					System.out.println("sql == > " + sql);
					for (int j = 0; j < lsatb.size(); j++){
						atb = (Map) lsatb.get(j);
						amount = isud.chkData(atb.get("EXPENSE_AMOUNT"));
						net = amount;
						sum = sum + Double.parseDouble(amount);
						String accNo = isud.chkData(atb.get("VENDER_ACCOUNT"));
						String bankCode = isud.chkData(atb.get("VENDER_BANK_CODE"));
						
						branchCode = "";
						if (bankCode.equals("034")) {
							if (accNo.trim().length() == 10) {
								branchCode = "0" + accNo.substring(0, 3);
							} else if (accNo.trim().length() == 12) {
								branchCode = "0" + accNo.substring(2, 5);
							}
						} else {
							branchCode = isud.chkData(atb.get("VENDER_BANK_BRANCH_CODE"));
							if (branchCode != null && !branchCode.equals("")) {
								if (branchCode.trim().length() < 4) {
									for (int x=0;x<(4-branchCode.trim().length());x++) {
										branchCode = "0"+branchCode;
									}
								}
							}else {
								if (accNo.trim().length() == 10) {
									branchCode = "0" + accNo.substring(0, 3);
								} else if (apab.getProvinceAccountSaving().trim().length() == 12) {
									branchCode = "0" + accNo.substring(2, 5);
								}
							}
						}

						detail.append(nsb.fulFill("1", 1));
						detail.append(nsb.fulFill(nsb.numberFormat("000000", String.valueOf(j + 2)), 6));
						detail.append(nsb.fulFill(nsb.yyyy() + nsb.mm() + nsb.getTomorrow(), 8));
						detail.append(nsb.fulFill(bankCode, 3));
						detail.append(nsb.fulFill(branchCode, 4));
						detail.append(nsb.fulFill(accNo, 16));
						detail.append(nsb.fulFill("C", 1));
						detail.append(nsb.fulFill(nsb.numberFormat("0000000000.00",  amount).replaceAll("\\.", ""), 12));
						detail.append(nsb.fulFill("N", 1));
						detail.append(nsb.fulFill(nsb.numberFormat("0000000", "0"), 7));
						detail.append(nsb.fulFill(nsb.numberFormat("0000000", "0"), 7));
						detail.append(nsb.fulFill(nsb.numberFormat("0000000", "0"), 7));
						detail.append(nsb.fulFill(nsb.numberFormat("0000000000.00", net).replaceAll("\\.", ""), 12));
						detail.append(nsb.fulFill("??", 2));
						detail.append(nsb.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_TH")), 40));
						detail.append(nsb.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_TH")), 60));
						detail.append(nsb.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_EN")), 40));
						detail.append(nsb.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_EN")), 60));
						detail.append(nsb.fulFill(isud.chkData(atb.get("TRANSACTION_TIMESTAMP")), 81));
						detail.append(nsb.fulFill(" ",32));
						detail.append("\r\n");
						sumAmtOut += Double.parseDouble(amount);
						countOut++;
					}
				}
				//TOTAL RECORD
				total.append(nsb.fulFill("2",1));
				total.append(nsb.fulFill(nsb.numberFormat("000000", String.valueOf(lsatb.size() + 2)),6));
				total.append(nsb.fulFill(nsb.numberFormat("00000000", String.valueOf(lsatb.size())),8));
				total.append(nsb.fulFill(nsb.formatDecimal(sum,2),15));
				total.append(nsb.fulFill(" ",242));
				total.append(nsb.fulFill(" ",128));
				
				if (lsatb.size() > 0) {
					cf++;
					branchCode = "";
					if (apab.getProvinceAccountSaving().trim().length() == 10) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(0, 3);
					} else if (apab.getProvinceAccountSaving().trim().length() == 12) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(2, 5);
					}
					String[] arrStr = ConfigBundle.getValue("BRANCH_CODE").split("\\,");
					if (arrStr.length > 1) {
						for (int x=0;x<arrStr.length;x++) {
							String[] arrBuffer = arrStr[x].split("\\|");
							if (apab.getProvinceId().equals(arrBuffer[0])) {
								branchCode = arrBuffer[1];
								break;
							}
						}
					}

					text.append(header).append(detail).append(total);
					System.out.println("text == > " + text.toString());
					InputStream is = new ByteArrayInputStream(text.toString().getBytes("TIS-620"));
					String destFolder = "/FromNhso/"+strArr[2]+"/"+strArr[1]+"/"; 
					String nameOfFile = "HSODC3"+strArr[2]+strArr[1]+strArr[0]+"CA" + nsb.numberFormat("000", String.valueOf(cf)) + ".0592"+branchCode;
					/*FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
					FileWriter file = new FileWriter(nsb.ftpBackup+"\\"+nameOfFile);
					BufferedWriter out = new BufferedWriter(file);
					out.write(text.toString());
					out.close();*/
					Acc6FtpBean afb = new Acc6FtpBean();
					afb.setFileName(nameOfFile);
					List ls = isud.selectList(afb);
					if (ls.size() == 0) {
						try {
							int status = FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
							if (FileTransferStatus.SUCCESS == status) {
								afb.setFtpDesc("got ftp-ed successfully to  folder " + destFolder + "/" + nameOfFile);
							} else if(FileTransferStatus.FAILURE == status) {
								afb.setFtpDesc("Fail to ftp  to  folder "+destFolder);
								afb.setFtpStatus("F");
							}
						} catch (Exception e) {
							afb.setFtpDesc("Fail to ftp  to  folder "+e.getMessage());
							afb.setFtpStatus("F");
							e.printStackTrace();
						}
						isud.insert(afb);
					}
				}
			}
			StringBuffer sb = new StringBuffer();
			DigitUtil du = new DigitUtil();
			sb.append("สรุปข้อมูลที่โอนเงิน ภายใน ธกส. จำนวน "+countIn+" ราย จำนวนเงิน "+du.formatDecimal(sumAmtIn, 2)+" บาท  \n");
			sb.append("สรุปข้อมูลที่โอนเงิน ต่างธนาคา "+countOut+" ราย จำนวนเงิน "+du.formatDecimal(sumAmtOut, 2)+" บาท   \n\n");
			sb.append("ผู้ประสานงาน น.ส.ปิติมา บุญฤทธิ์ภักดี 090-197-5097 pitima.b@nhso.go.th , น.ส.ณิชารีย์ พิเศษภักดีพงศ์ 02-141-5058 nicharee.b@nhso.go.th");
			String destFolder = "/FromNhso/"+nsb.yyyy()+"/"+nsb.mm()+"/"; 
			String nameOfFile = "SUMMARY"+nsb.yyyy()+nsb.mm()+nsb.dd()+"";
			InputStream is = new ByteArrayInputStream(sb.toString().getBytes("TIS-620"));
			FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
			
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		
	}

}



